''' 课程列表、实验操作相关的处理程序 '''
from http.client import responses

from jinja2 import TemplateNotFound
import tornado
from tornado import web, gen
from tornado.httputil import url_concat

import os
import shutil
import json
import logging
import pymysql

from .base import BaseHandler
from utils.dbutil import MySQLConnection
from utils.pathutil import PathBuilder

# 【所有课程】页
class CoursesHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        user = self.get_current_user()
        self.render('courses.html', user=user)

# 处理课程级别的信息
class CourseHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = 'select courseid, coursename, code, imagename, course.imageid, course.status from course inner join image on course.imageid=image.imageid'
                cursor.execute(sql)
                result = cursor.fetchall()
        finally:
            conn.close()

        self.write(json.dumps(result, ensure_ascii=True))
        self.finish()
    
    @web.authenticated
    @gen.coroutine
    def put(self):
        result = {'code':'success', 'message':''}

        course = json.loads(self.request.body.decode())
        courseid = course['courseid']
        coursename = course['coursename']
        courseimageid = course['courseimageid']
        status = int(course['status'])
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "update course set coursename='{}', imageid={}, status={} where courseid={}"
                sql_formatted = sql.format(coursename, courseimageid, status, courseid)
                cursor.execute(sql_formatted)
                conn.commit()
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'更新课程信息失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

    @web.authenticated
    @gen.coroutine
    def post(self):
        result = {'code':'success', 'message':''}

        course = json.loads(self.request.body.decode())    
        coursename = course['coursename']
        courseimageid = course['courseimageid']
        status = int(course['status'])
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                cursor.callproc('course_new', args=(coursename, courseimageid, status))
                conn.commit()
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'新增课程信息失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()
    
    @web.authenticated
    @gen.coroutine
    def delete(self):
        result = {'code':'success', 'message':''}

        courseid = self.get_argument("id")  
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = 'select code, status from course where courseid=' + courseid
                cursor.execute(sql)
                course = cursor.fetchone()
                status = course['status']
                code = course['code']
                if status == 1:                 # 如果该课程已经上架，则不能删除
                    result['code'] = 'error'
                    result['message'] = '无法删除已上架的课程'
                else:                           
                    # 先从数据库中删除
                    sql = "delete from lab where left(code,2)='{}'"
                    sql_formatted = sql.format(code)
                    cursor.execute(sql_formatted)
                    sql = "delete from chapter where left(code,2)='{}'"
                    sql_formatted = sql.format(code)
                    cursor.execute(sql_formatted)
                    sql = "delete from course where courseid='{}'"
                    sql_formatted = sql.format(courseid)
                    cursor.execute(sql_formatted)
                    conn.commit()

                    # 再从磁盘上删除
                    work_dir = PathBuilder.get_library_dir()
                    items = os.listdir(work_dir)
                    items_to_delete = []
                    for item in items:
                        if item[0:2]==code:
                            items_to_delete.append(os.path.join(work_dir, item))
                    for item in items_to_delete:
                        shutil.rmtree(item)
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'删除课程失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

# 【课程章节信息】页
class ChaptersHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self, id):
        user = self.get_current_user()
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = 'select coursename from course where courseid=' + id
                cursor.execute(sql)
                course = cursor.fetchone()
        finally:
            conn.close()
        
        self.render('chapters.html', user=user, coursename=course['coursename'])

# 处理章级别的信息
class ChapterHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        id = self.get_argument("id")
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = 'select chapterid, chaptername, code from chapter where courseid=' + id
                cursor.execute(sql)
                chapters = cursor.fetchall()
                
                sql = 'select a.chapterid, b.labid, b.labname, b.code, b.labtype, b.enabled \
                    from chapter as a inner join lab as b on a.chapterid=b.chapterid where a.courseid=' + id
                cursor.execute(sql)
                labs = cursor.fetchall()
        finally:
            conn.close()

        self.write(json.dumps({'chapters':chapters, 'labs':labs}, ensure_ascii=True))
        self.finish()
    
    @web.authenticated
    @gen.coroutine
    def post(self):
        result = {'code':'success', 'message':''}
        chapter = json.loads(self.request.body.decode())
        courseid = int(chapter['courseid'])
        chaptername = chapter['chaptername']
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                chapterid = 0
                code = '0'
                cursor.callproc('chapter_new', args=(courseid, chaptername, chapterid, code))
                cursor.execute('select @_chapter_new_2,@_chapter_new_3')
                out_params = cursor.fetchone()
                conn.commit()
                chapterid = out_params['@_chapter_new_2']
                code = out_params['@_chapter_new_3']
                result['message'] = {'chapterid':chapterid, 'code':code}
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'新增章信息失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()
    
    @web.authenticated
    @gen.coroutine
    def put(self):
        result = {'code':'success', 'message':''}
        chapter = json.loads(self.request.body.decode())
        chapterid = int(chapter['chapterid'])
        chaptername = chapter['chaptername']
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "update chapter set chaptername='{0}' where chapterid={1}".format(chaptername, chapterid)
                cursor.execute(sql)
                conn.commit()
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'修改章信息失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()
    
    @web.authenticated
    @gen.coroutine
    def delete(self):
        result = {'code':'success', 'message':''}

        data = json.loads(self.request.body.decode())
        chapterid = int(data['chapterid'])
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = 'select code from chapter where chapterid={}'.format(chapterid)
                cursor.execute(sql)
                chapter = cursor.fetchone()
                code = chapter['code']

                sql = "select a.labid from lab as a inner join assignment as b \
                    on a.code=b.labcode where a.chapterid={}".format(chapterid)
                cursor.execute(sql)
                labids = cursor.fetchone()
                if labids:                  # 如果该课程中的某些实验已经有学生使用，则不能删除
                    result['code'] = 'error'
                    result['message'] = '无法删除已有学生实训的课程'
                else:                       # 可以删除该章
                    # 先从数据库中删除
                    sql = "delete from lab where chapterid={}"
                    sql_formatted = sql.format(chapterid)
                    cursor.execute(sql_formatted)
                    sql = "delete from chapter where chapterid={}"
                    sql_formatted = sql.format(chapterid)
                    cursor.execute(sql_formatted)
                    conn.commit()

                    # 再从磁盘上删除
                    work_dir = PathBuilder.get_library_dir()
                    items = os.listdir(work_dir)
                    items_to_delete = []
                    for item in items:
                        if os.path.isdir(os.path.join(work_dir, item)) and item[0:4]==code:
                            items_to_delete.append(os.path.join(work_dir, item))
                    for item in items_to_delete:
                        shutil.rmtree(item)
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'删除章失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

# 处理实验级别的信息
class LabHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self, id):
        user = self.get_current_user()
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = 'select a.courseid, a.coursename, b.chapterid, b.chaptername, c.labid, c.labname, \
                    c.labtype, c.code, c.enabled \
                    from course as a left join chapter as b on a.courseid=b.courseid \
                    left join lab as c on b.chapterid=c.chapterid where c.labid=' + id
                cursor.execute(sql)
                lab = cursor.fetchone()
        finally:
            conn.close()
        self.render('labcontent.html', user=user, lab=lab)

    @web.authenticated
    @gen.coroutine
    def put(self):
        result = {'code':'success', 'message':''}
        lab = json.loads(self.request.body.decode())
        labid = int(lab['labid'])
        labname = lab['labname']
        labtype = lab['labtype']
        enabled = lab['enabled']
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "update lab set labname='{}', labtype={}, enabled={} where labid={}".format(
                    labname, labtype, enabled, labid
                )
                cursor.execute(sql)
                conn.commit()
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'修改实验信息失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()
    
    @web.authenticated
    @gen.coroutine
    def post(self):
        result = {'code':'success', 'message':''}
        lab = json.loads(self.request.body.decode())
        chapterid = int(lab['chapterid'])
        labname = lab['labname']
        labtype = int(lab['labtype'])
        enabled = int(lab['enabled'])
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                labid = 0
                code = '0'
                cursor.callproc('lab_new', args=(chapterid, labname, labtype, enabled, labid, code))
                cursor.execute('select @_lab_new_4,@_lab_new_5')
                out_params = cursor.fetchone()
                conn.commit()
                labid = out_params['@_lab_new_4']
                code = out_params['@_lab_new_5']
                result['message'] = {'labid':labid, 'code':code}
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'新增章信息失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

    @web.authenticated
    @gen.coroutine
    def delete(self):
        result = {'code':'success', 'message':''}

        data = json.loads(self.request.body.decode())
        labid = int(data['labid'])
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = 'select code from lab where labid={}'.format(labid)
                cursor.execute(sql)
                lab = cursor.fetchone()
                code = lab['code']

                sql = "select labid from assignment where labid={}".format(labid)
                cursor.execute(sql)
                labids = cursor.fetchone()
                if labids:                  # 如果该实验已经有学生使用，则不能删除
                    result['code'] = 'error'
                    result['message'] = '无法删除已有学生实训的实验'
                else:                       # 可以删除该实验
                    # 先从数据库中删除
                    sql = "delete from lab where labid={}"
                    sql_formatted = sql.format(labid)
                    cursor.execute(sql_formatted)
                    conn.commit()

                    # 再从磁盘上删除
                    lab_dir = PathBuilder.get_library_dir_for_lab(code)
                    if os.path.exists(lab_dir):
                        shutil.rmtree(lab_dir)
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'删除实验失败!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

class LabContentHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        labid = self.get_argument("id")
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = 'select guides from lab where labid=' + labid
                cursor.execute(sql)
                guides = cursor.fetchone()
        finally:
            conn.close()
        
        self.write(json.dumps(guides))
        self.finish()
    
    @web.authenticated
    @gen.coroutine
    def put(self):
        result = {'code':'success', 'message':''}
        lab = json.loads(self.request.body.decode())
        labid = lab['labid']
        guides = lab['guides']
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "update lab set guides='{}' where labid={}"
                sql_formatted = sql.format(guides, labid)
                cursor.execute(sql_formatted)
                conn.commit()
        except Exception as ex:
            result['code'] = 'error'
            result['message'] = r'修改实验指导成功!'
            logging.error(ex)
        finally:
            conn.close()
        
        self.write(json.dumps(result))
        self.finish()

class LabResourceHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        code = self.get_argument("code")
        base_dir = PathBuilder.get_library_dir()
        length_prefix = len(base_dir)
        lab_resource_dir = PathBuilder.get_library_dir_for_lab(code)
        entities = os.walk(lab_resource_dir)
        items = []
        for dirpath, _, filenames in entities:
            for filename in filenames:
                name = filename
                path = dirpath[length_prefix : ]
                items.append({"name":name, "path":path})
        
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "select labfile from lab where code='{}'".format(code)
                cursor.execute(sql)
                labfile = cursor.fetchone()['labfile']
        finally:
            conn.close()
                
        self.finish({'files':items, 'labfile':labfile})


    @web.authenticated
    @gen.coroutine
    def post(self):
        result = {'code':'success', 'message':''}
        code = self.get_argument('code')
        lab_file = self.get_argument('labfile')

        # 更新labfile的信息
        conn = MySQLConnection.get_connection()
        try:
            with conn.cursor() as cursor:
                sql = "update lab set labfile='{}' where code='{}'".format(lab_file, code)
                cursor.execute(sql)
                conn.commit()
        finally:
            conn.close()

        resource_file = self.request.files.get('resource_file')
        if resource_file:
            uploaded_file = resource_file[0]
            
            base_dir = PathBuilder.get_library_dir_for_lab(code)
            if os.path.exists(base_dir):
                shutil.rmtree(base_dir)
            os.mkdir(base_dir)
            
            zip_file_path = os.path.join(base_dir, code + ".zip") 
            with open(zip_file_path, 'wb') as up:
                up.write(uploaded_file['body'])
                
            shutil.unpack_archive(zip_file_path, extract_dir=base_dir, format='zip')
            os.system("rm -f " + zip_file_path)

        self.write(json.dumps(result))
        self.finish()

class DownloadLabResourceHandler(BaseHandler):
    @web.authenticated
    @gen.coroutine
    def get(self):
        code = self.get_argument('code')
        lab_resource_dir = PathBuilder.get_library_dir_for_lab(code)
        shutil.make_archive(lab_resource_dir, 'zip', lab_resource_dir)
        zip_filepath = lab_resource_dir + ".zip"
        
        self.set_header ('Content-Type', 'application/octet-stream')
        self.set_header ('Content-Disposition', 'attachment; filename=' + code + ".zip")
        buf_size = 8192
        with open(zip_filepath, 'rb') as f:
            while True:
                data = f.read(buf_size)
                if not data:
                    break
                self.write(data)
        os.system("rm -f " + zip_filepath)
        self.finish()
            

default_handlers = [
    (r'/lab_courses', CoursesHandler),
    (r'/lab_getcourselist', CourseHandler),
    (r'/lab_editcourse', CourseHandler),
    (r'/lab_newcourse', CourseHandler),
    (r'/lab_deletecourse', CourseHandler),
    (r'/lab_course/(?P<id>\d*)', ChaptersHandler),
    (r'/lab_getchapterlist', ChapterHandler),
    (r'/lab_newchapter', ChapterHandler),
    (r'/lab_editchapter', ChapterHandler),
    (r'/lab_deletechapter', ChapterHandler),
    (r'/lab_newlab', LabHandler),
    (r'/lab_editlab', LabHandler),
    (r'/lab_deletelab', LabHandler),
    (r'/lab_lab/(?P<id>\d*)', LabHandler),
    (r'/lab_getlabcontent', LabContentHandler),
    (r'/lab_updatelabcontent', LabContentHandler),
    (r'/lab_getlabresource', LabResourceHandler),
    (r'/lab_uploadlabresource', LabResourceHandler),
    (r'/lab_downloadlabresource', DownloadLabResourceHandler),
]